-- table Stock
create sequence stock_sequence;
create table stock (
	id bigint default stock_sequence.nextval,
	symbol varchar(10),
	name varchar(255),
	createdon timestamp,
	modifiedon timestamp,
	modifiedby varchar(50),
	createdby varchar(50)
);

alter table stock  alter column id set not null;
alter table stock  alter column name set not null;
alter table stock  alter column symbol set not null;
alter table stock  add primary key(id);


-- Table StockDaily
create sequence stock_daily_sequence;
create table StockDaily (
	id bigint default stock_daily_sequence.nextval not null primary key,
	datetime timestamp not null,
	open double not null,
	low double not null,	
	high double not null,
	close double not null,
	percentPriceChange decimal(10,4),
	priceChange double,
	volume bigint,
	averageVolume bigint
);
alter table StockDaily add column stockId bigint not null;
alter table StockDaily add foreign key(stockId) references stock(id);

-- table StockWeekly
create sequence stock_weekly_sequence;
create table StockWeekly (
	id bigint default stock_weekly_sequence.nextval not null primary key,
	--stockId bigint not null foreign key(stockId) references stock(id),
	datetime timestamp not null,
	open double not null,
	low double not null,	
	high double not null,
	close double not null,
	percentPriceChange decimal(10,4),
	priceChange double,
	volume bigint,
	averageVolume bigint
);
alter table StockWeekly add column stockId bigint not null;
alter table StockWeekly add foreign key(stockId) references stock(id);

-- table StockMovingAverage
create sequence stock_moving_average_sequence;
create table StockMovingAverage (
	id bigint default stock_moving_average_sequence.nextval not null primary key,
	stockId bigint not null,
	sma50 double,
	sma200 double,
	sma20 double,
	ema10 double,
	ema8 double
);
alter table StockMovingAverage add foreign key(stockId) references stock(id);